<?php
namespace app\sw\service;

use app\sw\model\FileModel;
use app\sw\model\FileTemplateModel;
use app\sw\model\SheetDataModel;
use app\sw\model\SheetModel;
use app\sw\model\SheetTemplateModel;
use think\admin\model\SystemFile;
use think\facade\Db;
use think\file\UploadedFile;

class SheetService{


    public static function replaceDefaultData($year, $month, $str){
        $next_month = intval(date('m', strtotime('+1 month', strtotime($year . '-' . $month . '-01'))));
        $next_month_year = intval(date('Y', strtotime('+1 month', strtotime($year . '-' . $month . '-01'))));
        $variables = array(
            '{year}' => intval($year),
            '{month}' => intval($month),
            '{next_month}' => $next_month,
            '{next_month_year}' => $next_month_year,
            // 添加更多的变量和值
        );
        return str_replace(array_keys($variables), array_values($variables), $str);
    }

    public static function createWorkSheet($year, $month){
        $sheetList = SheetModel::where(['s_year' => $year, 's_month' => $month])->select();
        // 通过模板，生成对应的工作表
        if($sheetList->toArray()){
            // 有数据，不需要生成
            throw new \Exception('当月的数据已创建，无法再次创建该月数据');
        }
        Db::startTrans();
        try {
            $file_template_list = FileTemplateModel::with(['sheet_template'])->where(['status' => 1])->select();
            foreach ($file_template_list as $key => $file_template) {
                // 创建文件记录
                $fileData = [
                    'file_template_id' => $file_template->id,
                    'file_name' => $file_template->file_name,
                    'create_time' => time(),
                    'update_time' => time(),
                    'ym_str' => "$year-$month",
                    'ym_timestamp' => strtotime("$year-$month-01"),
                    'year' => $year,
                    'month' => $month,
                ];
                $file_id = FileModel::insertGetId($fileData);
                // 通过模板，生成对应的工作表
                $templateList = $file_template->sheet_template;
                if(!$templateList){
                    throw new \Exception('没有可用的模板');
                }
                // 月模板存储路径
                $monthPath = SITE_PATH . DS . 'public' . DS . 'template' . DS . $year . DS . $month . DS;
                if(!is_dir($monthPath)){
                    mkdir($monthPath, 0777, true);
                }
                foreach ($templateList as $item) {
                    # code...
                    $template_path = SITE_PATH . DS . 'public' . DS . $item['template_path'];
                    if(!file_exists($template_path)){
                        throw new \Exception($item['template_path'] . '模板文件不存在');
                    }
                    // 替换模板中的年月变量
                    $spreadsheet = \PHPExcel_IOFactory::load($template_path);
                    // 获取活动工作表
                    $sheet = $spreadsheet->getActiveSheet();
                    // 要替换的变量和对应的值
  
                    $replaceCell = explode(',', $item['replace_cell']);
                    foreach ($replaceCell as $cell) {
                        if($cell){
                            try {
                                # code...
                                $cellValue = $sheet->getCell($cell)->getValue();
                                // 替换变量
                                $newCellValue = self::replaceDefaultData($year, $month, $cellValue);
                                // 设置新值到单元格
                                $sheet->setCellValue($cell, $newCellValue);
                            } catch (\Throwable $th) {
                                //throw $th;
                            }
                        }
                    }
                    // 获取sheet名字，并替换变量
                    $sheetName = $sheet->getTitle();
                    $newSheetName = self::replaceDefaultData($year, $month, $sheetName);;
                    $sheet->setTitle($newSheetName);
                    $sheet_name = self::replaceDefaultData($year, $month, $item['sheet_name']);
                    // 保存修改后的 Excel 文件
                    $writer = \PHPExcel_IOFactory::createWriter($spreadsheet, 'Excel2007');
                    $file_save_path = $monthPath . $sheet_name . '.xlsx';
                    $writer->save($file_save_path);
                    // 保存表记录到数据库。
                    $sheetData = [
                        's_year' => $year,
                        's_month' => $month,
                        'template_id' => $item['id'],
                        'sheet_name' => $sheet_name,
                        'excel_file_id' => $file_id,
                        'dept_id' => $item['department_id'],
                        'sheet_type' => $item['sheet_type'],
                        'file_template_id' => $item['file_template_id'],
                        'sort' => $item['sort'],
                        'template_path' => $template_path,
                        'file_path' => $file_save_path,
                        'create_time' => time(),
                        'update_time' => time(),
                    ];
                    $sheetModel = new SheetModel();
                    $sheetModel->save($sheetData);
                    // 保存表数据到数据库
                    if($item['fields']){
                        $sheetRows = $item['fields'];
                        $sheetFieldData = [];
                        foreach ($sheetRows as $row) {
                            $sheetFieldData[] = [
                                'sheet_id' => $sheetModel->id,
                                'field_name' => $row['field'],
                                'department_id' => $row['department_id'],
                                'excel_row_index' => $row['excel_row_index'],
                                'excel_start_cloum_index' => $row['excel_start_cloum_index'],
                            ];
                        }
                        if($sheetFieldData){
                            // dump($sheetFieldData);
                            $sheetModel = new SheetDataModel();
                            $sheetModel->insertAll($sheetFieldData);
                        }
                    }
                }
            }

            Db::commit();
        } catch (\Throwable $th) {
            //throw $th;
            Db::rollback();
            throw $th;
        }
    }


    public static function saveSheetDataToExcel($sheetObj){
        $datas = $sheetObj->datas;
        // 将数据保存至excel文件
        $spreadsheet = \PHPExcel_IOFactory::load($sheetObj->file_path);
        // 获取活动工作表
        $sheet = $spreadsheet->getActiveSheet();
        foreach ($datas as $row) {
            $start_cloum_index = $row['excel_start_cloum_index'];
            $excel_row_index = $row['excel_row_index'];
            for ($i=1; $i <= 13 ; $i++) { 
                # code...
                $cell_index = $start_cloum_index . $excel_row_index;
                if($row['data' . $i]){
                    $sheet->setCellValue($cell_index, $row['data' . $i]);
                }
                $start_cloum_index++;
            }
        }
        // 保存修改后的 Excel 文件
        $writer = \PHPExcel_IOFactory::createWriter($spreadsheet, 'Excel2007');
        $file_save_path = SITE_PATH . DS . 'public' . DS . 'upload' . DS . 'shangwu' . DS . date('Ym');
        $originalName = $sheetObj['sheet_name'] . '.xlsx';
        $file_save_path = $file_save_path . DS . $originalName;
        $writer->save($file_save_path);
        $fileRecord = self::saveFileRecord($file_save_path);
        $sheetObj->result_file_id = $fileRecord['id'];
        $sheetObj->save();
        return true;
    }

    public static function saveFileRecord($file_path){


        $originalName = basename($file_path);  // 文件原名
        $file_save_path = dirname($file_path);
        // 临时存储文件。
        
        if(!is_dir($file_save_path)){
            mkdir($file_save_path, 0755, true);
        }
        
        $xkey = str_replace(SITE_PATH . DS . 'public' . DS, '', $file_path);
        $xkey = str_replace('/', DS, $xkey);
        $xkey = str_replace('\\', DS, $xkey);

        $hash = md5($file_path); 
        $fileRecord = SystemFile::where(['hash' => $hash])->find();
        if(!$fileRecord){

            $data = [
                'name' => $originalName,
                'hash' => $hash,
                'type' => 'local',
                'xext' => 'xlsx',
                'xkey' => $xkey,
                'size' => filesize($file_path),
                'xurl' => filePathToUrl($file_path),
            ];
    
            $insert_id = SystemFile::insertGetId($data);
            $data['id'] = $insert_id;
            return $data;
        }

        return $fileRecord;
    }



}